﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.Common;
using Microsoft.Practices.EnterpriseLibrary.Data;
using DuJianliang.ApplicationPlatform.Common;
using DuJianliang.ApplicationPlatform.Model;
using DuJianliang.ApplicationPlatform.UserPermission;

namespace DuJianliang.ApplicationPlatform.TempEquipment
{
    public class TempEquipmentDal
    {
        
        public void TempEquipment_Insert(Model.TempEquipmentOB obj)
        {
            string sql = @"INSERT INTO [TempEquipment]
                           ([FlowInstanceID]
                           ,[applyDepartment]
                           ,[applyPerson]
                           ,[applyCode]
                           ,[usedFor]
                           ,[applyReason]
                           ,[needDate]
                           ,[factDate]
                           ,[CreateUser])
                     VALUES
                           (@FlowInstanceID
                           ,@applyDepartment
                           ,@applyPerson
                           ,@applyCode
                           ,@usedFor
                           ,@applyReason
                           ,@needDate
                           ,@factDate
                           ,@CreateUser)";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.String, obj.FlowInstanceID);
                database.AddInParameter(dbCommand, "@applyDepartment", DbType.String, obj.applyDepartment);
                database.AddInParameter(dbCommand, "@applyPerson", DbType.String, obj.applyPerson);
                database.AddInParameter(dbCommand, "@applyCode", DbType.String, obj.applyCode);
                database.AddInParameter(dbCommand, "@usedFor", DbType.String,obj.usedFor);
                database.AddInParameter(dbCommand, "@applyReason", DbType.String, obj.applyReason);
                database.AddInParameter(dbCommand, "@needDate", DbType.String, obj.needDate);
                database.AddInParameter(dbCommand, "@factDate", DbType.String, obj.factDate);
                database.AddInParameter(dbCommand, "@CreateUser", DbType.String, CurrentInfo.Instance.CurrentUser.UserName);
  
                database.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void TempEquipment_Update(Model.TempEquipmentOB obj)
        {
            string sql = @"UPDATE [TempEquipment]
                           SET [usedFor] = @usedFor
                              ,[applyReason] = @applyReason
                              ,[needDate] = @needDate
                              ,[factDate] = @factDate
                         WHERE FlowInstanceID=@FlowInstanceID";
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.String, obj.FlowInstanceID);

                database.AddInParameter(dbCommand, "@usedFor", DbType.String, obj.usedFor);
                database.AddInParameter(dbCommand, "@applyReason", DbType.String, obj.applyReason);
                database.AddInParameter(dbCommand, "@needDate", DbType.String, obj.needDate);
                database.AddInParameter(dbCommand, "@factDate", DbType.String, obj.factDate);

                database.ExecuteNonQuery(dbCommand);
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void TempEquipment_Delete(string FlowInstanceID)
        {
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                string sql = @"DELETE FROM [TempEquipment]  WHERE FlowInstanceID=@FlowInstanceID";

                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.String, FlowInstanceID);
                database.ExecuteNonQuery(dbCommand);

                string sql2 = @"DELETE FROM [TempEquipmentDetial]  WHERE ParentID=@ParentID";
                DbCommand dbCommand2 = database.GetSqlStringCommand(sql2);
                database.AddInParameter(dbCommand2, "@ParentID", DbType.String, FlowInstanceID);
                database.ExecuteNonQuery(dbCommand);
            }
            catch (Exception)
            {                
                throw;
            }           
        }

        public Model.TempEquipmentOB getTempEquipment(string FlowInstanceID)
        {
            string sql = @"select * from TempEquipment where FlowInstanceID=@FlowInstanceID";
            Database database = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = database.GetSqlStringCommand(sql);
            database.AddInParameter(dbCommand, "@FlowInstanceID", DbType.String, FlowInstanceID);
            Model.TempEquipmentOB obj = null;
            using (IDataReader r = database.ExecuteReader(dbCommand))
            {              
                while (r.Read())
                {
                    obj = new Model.TempEquipmentOB();

                    obj.FlowInstanceID = r["FlowInstanceID"] != null ? r["FlowInstanceID"].ToString() : string.Empty;
                    obj.applyCode = r["applyCode"].ToString();
                    obj.applyDepartment = r["applyDepartment"].ToString();
                    obj.applyPerson = r["applyPerson"].ToString();
                    obj.applyReason = r["applyReason"].ToString();
                    obj.usedFor = r["usedFor"].ToString();
                    obj.needDate = r["needDate"].ToString();
                    obj.factDate = r["factDate"].ToString();
                    obj.CreateDate = r["CreateDate"].ToString();                  
                }
            }
            return obj;
        }

        public DataTable GetTempEquipmentDataTable(string flowOperation)
        {
//            string sql = @"SELECT A.*, C.FlowID, B.StepID, B.StepName, B.OperationName, B.OperationDate, B.OperatorInfo, B.Completed, B.CreateDate AS FlowCreateDate
//                            FROM dbo.TempEquipment A 
//                            LEFT JOIN dbo.FlowInstance B ON A.FlowInstanceID = B.FlowInstanceID
//                            LEFT JOIN dbo.FlowMain C ON C.TableName = 'TempEquipment' ORDER BY CreateDate DESC";
//            Database database = DatabaseFactory.CreateDatabase();
//            DbCommand dbCommand = database.GetSqlStringCommand(sql);
//            DataTable dataTable = database.ExecuteDataSet(dbCommand).Tables[0];

            string sql = @"SELECT A.*, B.StepID, B.StepName, B.OperationName, B.OperationDate, B.OperatorInfo, B.Completed, B.CreateDate AS FlowCreateDate
                            ,
                            (
                              SELECT TOP 1 FSR.FlowID FROM dbo.FlowStepRelation FSR 
                              INNER JOIN dbo.FlowStepOperator FSO ON FSR.StepID = FSO.StepID
                              INNER JOIN dbo.FlowMain FM ON FSR.FlowID = FM.FlowID AND FM.TableName = 'TempEquipment'
                              WHERE FSR.ParentID IS NULL AND FSO.OperatorInfo = @OperatorInfo
                            ) AS FlowID
                            FROM dbo.TempEquipment A 
                            LEFT JOIN dbo.FlowInstance B ON A.FlowInstanceID = B.FlowInstanceID {0} 
                            LEFT JOIN dbo.t5715_User C ON A.CreateUser = C.UserName
                            WHERE A.CreateUser = @OperatorInfo {1}
                            ORDER BY A.CreateDate DESC";


            string strWhere = string.Empty;
            switch (flowOperation)
            {
                case "-1":
                    strWhere = string.Empty;
                    break;
                case "0":
                    strWhere = "WHERE B.OperationID IS NULL OR B.OperationID = ''";
                    break;
                case "1":
                    strWhere = "WHERE B.OperationID = 2 AND Completed = 1";
                    break;
                case "2":
                    strWhere = "WHERE B.OperationID = 4 AND Completed = 1";
                    break;
                case "3":
                    strWhere = "WHERE B.OperationID = 3";
                    break;
            }

            string childDeptWhere = string.Empty;
            string childDeptString = new UserPermissionBll().GetChildDeptIDString(CurrentInfo.Instance.CurrentUser.Dept.DeptID);
            if (!string.IsNullOrWhiteSpace(childDeptString))
            {
                childDeptWhere = string.Format("  OR C.DeptID IN ({0})", childDeptString);
            }

            sql = string.Format(sql, strWhere, childDeptWhere);

            Database database = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = database.GetSqlStringCommand(sql);
            database.AddInParameter(dbCommand, "@OperatorInfo", DbType.String, CurrentInfo.Instance.CurrentUser.UserName);
            DataTable dataTable = database.ExecuteDataSet(dbCommand).Tables[0];

            return dataTable;
        }

        public List<Model.TempEquipmentOB> getTempEquipmentList(string whereCondition)
        {
            string Condititon = string.Empty;
            if(!string.IsNullOrEmpty(whereCondition))
            {
                Condititon = " where " + whereCondition +" order by CreateDate desc";
            }

            List<Model.TempEquipmentOB> result = null;
            string sql = "select * from TempEquipment " + Condititon;
            sql = sql + Condititon;
            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                using (IDataReader r = db.ExecuteReader( CommandType.Text,sql))
                {
                    result = new List<Model.TempEquipmentOB>();
                    while (r.Read())
                    {
                        Model.TempEquipmentOB obj = new Model.TempEquipmentOB();

                        obj.FlowID = r["FlowID"] != null ? r["FlowID"].ToString() : string.Empty;
                        obj.applyCode = r["applyCode"].ToString();
                        obj.applyDepartment = r["applyDepartment"].ToString();
                        obj.applyPerson = r["applyPerson"].ToString();
                        obj.applyReason = r["applyReason"].ToString();
                        obj.usedFor = r["usedFor"].ToString();
                 
                        obj.needDate = r["needDate"] != null ? r["needDate"].ToString() : string.Empty;
                        obj.factDate = r["factDate"] != null ? r["factDate"].ToString() : string.Empty;
                        obj.FlowInstanceID = r["FlowInstanceID"] != null ? r["FlowInstanceID"].ToString() : string.Empty;
                       
                        obj.CreateDate = r["CreateDate"].ToString();

                        result.Add(obj);
                    }
                }
            }
            catch (Exception)
            {
                
                throw;
            }
            return result;
        }

        public string GetMaxCode()
        {
            string sql = @"select isnull(max(applyCode),0) from dbo.TempEquipment";
            Database db = DatabaseFactory.CreateDatabase();
            object maxCode = db.ExecuteScalar(CommandType.Text, sql);

            return maxCode.ToString();

        }
        /// <summary>
        /// 生成流水号
        /// </summary>
        /// <returns></returns>
        public string CreateApplyCode()
        {
            return GetSerialNumber(GetMaxCode());
        }


        public string GetSerialNumber(string serialNumber)
        {
            if (serialNumber != "0")
            {
                string headDate = serialNumber.Substring(0, 6);
                int lastNumber = int.Parse(serialNumber.Substring(6));
                //如果数据库最大值流水号中日期和生成日期在同一天，则顺序号加1
                if (headDate == DateTime.Now.ToString("yyMMdd"))
                {
                    lastNumber++;
                    return headDate + lastNumber.ToString("0000");
                }
            }
            return DateTime.Now.ToString("yyMMdd") + "0001";
        }

        public void TempEquipmentDetial_Insert(Model.TempEquipmentDetialOB obj)
        {
            string sql = @"INSERT INTO [TempEquipmentDetial]
                           ([ID]
                           ,[ParentID]
                           ,[equipmentName]
                           ,[Model]
                           ,[unit]
                           ,[amount]
                           ,[reMark]
                           )
                     VALUES
                           (newid()
                           ,@ParentID
                           ,@equipmentName
                           ,@Model
                           ,@unit
                           ,@amount
                           ,@reMark)";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@ParentID", DbType.String, obj.ParentID);
                database.AddInParameter(dbCommand, "@equipmentName", DbType.String, obj.equipmentName);
                database.AddInParameter(dbCommand, "@Model", DbType.String, obj.Model);
                database.AddInParameter(dbCommand, "@unit", DbType.String, obj.unit);
                database.AddInParameter(dbCommand, "@amount", DbType.String, obj.amount);
                database.AddInParameter(dbCommand, "@reMark", DbType.String, obj.reMark);

                database.ExecuteNonQuery(dbCommand);


             
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void TempEquipmentDetial_Update(Model.TempEquipmentDetialOB obj)
        {
            string sql = @"UPDATE [TempEquipmentDetial]
                           SET 
                               [equipmentName] = @equipmentName
                              ,[Model] = @Model
                              ,[unit] = @unit
                              ,[amount] = @amount
                              ,[reMark] = @reMark
                         WHERE ID=@ID";

            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@ParentID", DbType.String, obj.ParentID);
                database.AddInParameter(dbCommand, "@equipmentName", DbType.String, obj.equipmentName);
                database.AddInParameter(dbCommand, "@Model", DbType.String, obj.Model);
                database.AddInParameter(dbCommand, "@unit", DbType.String, obj.unit);
                database.AddInParameter(dbCommand, "@amount", DbType.String, obj.amount);
                database.AddInParameter(dbCommand, "@reMark", DbType.String, obj.reMark);
                database.AddInParameter(dbCommand, "@ID", DbType.String, obj.ID);


                database.ExecuteNonQuery(dbCommand);


               
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public void TempEquipmentDetial_Delete(string ID)
        {
            string sql = @"delete from TempEquipmentDetial where ID=@ID";
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@ID", DbType.String, ID);
                database.ExecuteNonQuery(dbCommand);
            }
            catch (Exception)
            {
                
                throw;
            }
        }

        public TempEquipmentDetialOB getTempEquipmentDetial(string ID)
        {
            string sql = @"select * from TempEquipmentDetial  where ID=@ID";
            TempEquipmentDetialOB obj = null;
            try
            {
                Database database = DatabaseFactory.CreateDatabase();
                DbCommand dbCommand = database.GetSqlStringCommand(sql);
                database.AddInParameter(dbCommand, "@ID", DbType.String, ID);

                using (IDataReader r = database.ExecuteReader(dbCommand))
                {
                    while (r.Read())
                    {
                        obj = new TempEquipmentDetialOB();
                        obj.ID = r["ID"].ToString();
                        obj.ParentID = r["ParentID"].ToString();
                        obj.equipmentName = r["equipmentName"].ToString();
                        obj.unit = r["unit"].ToString();
                        obj.amount = r["amount"].ToString();
                        obj.Model = r["Model"].ToString();
                        obj.reMark = r["reMark"].ToString();
                    } 
                }

            }
            catch (Exception)
            {
                
                throw;
            }
            return obj;

        }        

        public List<Model.TempEquipmentDetialOB> getTempEquipmentDetialList(string whereCondition)
        {
            string Condititon = string.Empty;
            if (!string.IsNullOrEmpty(whereCondition))
            {
                Condititon = " where " + whereCondition + " order by CreateDate desc";
            }

            List<Model.TempEquipmentDetialOB> result = null;
            string sql = "select * from TempEquipmentDetial " + Condititon;

            try
            {
                Database db = DatabaseFactory.CreateDatabase();
                using (IDataReader r = db.ExecuteReader(CommandType.Text, sql))
                {
                    result = new List<Model.TempEquipmentDetialOB>();
                    while (r.Read())
                    {
                        Model.TempEquipmentDetialOB obj = new Model.TempEquipmentDetialOB();

                        obj.ID = r["ID"].ToString();
                        obj.ParentID = r["ParentID"].ToString();
                        obj.equipmentName = r["equipmentName"].ToString();
                        obj.unit = r["unit"].ToString();
                        obj.amount = r["amount"].ToString();
                        obj.Model = r["Model"].ToString();
                        obj.reMark = r["reMark"].ToString();
                        

                        result.Add(obj);
                    }


                }
            }
            catch (Exception)
            {

                throw;
            }
            return result;
        }

    }
}